<?
	session_start();
	error_reporting(E_ALL);
	include('conn.php');	
	$tbl = $_REQUEST["table"];	
	$id = $_REQUEST["cud"];	
	$idno = $_REQUEST["id"];
	$user=$_SESSION['username'];
	
	switch($id)
	{
		case 'd':
			$qry = "update $tbl set status='9' where id='$idno';";			
			$res = mysql_query($qry);
			echo $qry."---".$res;
			break;
			
		case "u":
			$d = json_decode($_REQUEST["dataset"]);
			switch($tbl)
			{
				case 'replaceCard':					
					$q1="select oldcard,newcard from replaceCard where id='$idno';";
					$r1=mysql_fetch_array(mysql_query($q1));
					$enid = $r1['oldcard'];
					$eoid = $r1['newcard'];
					
					$q1 = "select id from stbTable where stbno = '$d->stb';";
					$r1 = mysql_fetch_array(mysql_query($q1));
					$sid = $r1['id'];
					$q2 = "select id from cardTable where cardno = '$d->oldcard';";
					$r2 = mysql_fetch_array(mysql_query($q2));
					$ocid = $r2['id'];
					$q3 = "select id from cardTable where cardno = '$d->newcard';";
					$r3 = mysql_fetch_array(mysql_query($q3));
					$ncid = $r3['id'];
					
					$qry = "update replaceCard set 
					newcard='$ncid', oldcard='$ocid', stbno='$sid', replacedate='$d->rdate', 
					reason='$d->reason', comments='$d->comments' where id='$idno'";
					$res = mysql_query($qry);
					echo $qry." - ".$res;									
					
					$q2="update STBDetails set cardid='$ncid',modifiedby='$user',modifieddate='".date('Y-m-d')."' where cardid='$enid';";
					$r2 = mysql_query($q2);					
					$q2="update STBtoCustomer set cardid='$ncid',modifiedby='$user',modifieddate='".date('Y-m-d')."' where cardid='$enid';";
					$r2 = mysql_query($q2);
					
					//if ($enid!=$nid)
					//{
						$q3="update cardTable set flag = 'free',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$enid';";
						$r3=mysql_query($q3);
						$q3="update cardTable set flag = 'occ',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$ncid';";
						$r3=mysql_query($q3);
					//}
					//if ($eoid!=$oid)
					//{
						$flagval = '';
						$cause = $d->reason;
						if($cause=='Faulty')
							$flagval = 'faulty';
						else if($cause=='Blacklist')						
							$flagval = 'blist';
						else
							$flagval = 'free';
						$q3="update cardTable set flag = 'occ',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$eoid';";
						$r3=mysql_query($q3);
						$q3="update cardTable set flag = '$flagval',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$ocid';";
						$r3=mysql_query($q3);
					//}
					break;
				
				case 'replaces':
					$q1="select newstb,oldstb from replaces where id='$idno';";
					$r1=mysql_fetch_array(mysql_query($q1));
					$enid = $r1['newstb'];					
					$eoid = $r1['oldstb'];
					
					$q3 = "select id from stbTable where stbno = '$d->newstb';";					
					$r3 = mysql_fetch_array(mysql_query($q3));	
					$nsid = $r3['id'];			
					$q3 = "select id from stbTable where stbno = '$d->oldstb';";					
					$r3 = mysql_fetch_array(mysql_query($q3));	
					$osid = $r3['id'];																			
					$r = mysql_fetch_array(mysql_query("select id from cardTable where cardno='$d->card'"));
					$cid = $r['id'];
					
					$qry = "update replaces set 
					newstb='$nsid', oldstb='$osid', cardno='$cid', replacedate='$d->rdate', 
					reason='$d->reason', comments='$d->comments' where id='$idno'";
					$res = mysql_query($qry);
					echo $qry."---".$res;										
					
					$q2="update STBDetails set stbid='$nsid',modifiedby='$user',modifieddate='".date('Y-m-d')."' where stbid='$enid';";
					$r2 = mysql_query($q2);					
					$q2="update STBtoCustomer set stbid='$nsid',modifiedby='$user',modifieddate='".date('Y-m-d')."' where stbid='$enid';";
					$r2 = mysql_query($q2);
					
					//if ($enid!=$nid)
					//{
						$q3="update stbTable set flag = 'free',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$enid';";
						$r3=mysql_query($q3);
						$q3="update stbTable set flag = 'occ',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$nsid';";
						$r3=mysql_query($q3);
					//}
					//if ($eoid!=$oid)
					//{
						$flagval = '';
						$cause = $d->reason;
						if($cause=='Faulty')
							$flagval = 'faulty';
						else if($cause=='Blacklist')						
							$flagval = 'blist';
						else
							$flagval = 'free';
						$q3="update stbTable set flag = 'occ',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$eoid';";
						$r3=mysql_query($q3); 
						$q3="update stbTable set flag = '$flagval',modifiedby='$user',modifieddt='".date('Y-m-d')."' where id='$osid';";
						$r3=mysql_query($q3);
					//}
					break;
				
				case 'replacePair':										
					$qry = "update replacePair set 
					newcard='$d->newcard', oldcard='$d->oldcard', oldstb='$d->oldstb',  newstb='$d->newstb',
					replacedate='$d->rdate', reason='$d->reason', comments='$d->comments'
					where id='$idno'";
					$res = mysql_query($qry);
					echo $qry."---".$res;															
			}
			break;
			
		case "c":
			$d = json_decode($_REQUEST["dataset"]);
			switch($tbl)
			{
				case 'replaceCard':
					$q1 = "select id from stbTable where stbno = '$d->stb';";
					$r1 = mysql_fetch_array(mysql_query($q1));
					$osid = $r1['id'];
					$q2 = "select id from cardTable where cardno = '$d->oldcard';";
					$r2 = mysql_fetch_array(mysql_query($q2));
					$ocid = $r2['id'];
					$q3 = "select id from cardTable where cardno = '$d->newcard';";
					$r3 = mysql_fetch_array(mysql_query($q3));
					$ncid = $r3['id'];					
					
					$qry = "insert into replaceCard(newcard,oldcard,stbno,replacedate,reason,comments,status) 
					values ('$ncid','$ocid','$osid','$d->rdate','$d->reason','$d->comments','1');";
					$res = mysql_query($qry);
					echo $qry."---".$res;

					$q4 = "select CustId,pairid,cardid,stbid,RateperMonth,TerminationDate,enteredby from STBtoCustomer where cardid = '$ocid' and stbid='$osid';";	
					$res2 = mysql_query($q4);					
					while($row2=mysql_fetch_array($res2)){						
						$custid = $row2['CustId'];
						$oldpairid = $row2['pairid'];															
						$rpm = $row2['RateperMonth'];
						$by = $row2['enteredby'];
						//$tdt = $row2['TerminationDate'];
					}
					echo "<br> $q4 - ".$row2;										
					
					$q4 = "select stbtypeid from STBDetails where pairid = '$oldpairid';";
					$r4 = mysql_fetch_array(mysql_query($q4));
					$stbtype = $r4['stbtypeid'];
					
					
					$qry6 = "insert into STBDetails(stbid,cardid,stbtypeid,Active,Notes,enteredby,entereddate) 
					values('$osid', '$ncid', '$stbtype', '1','created on replacement','$user','".date('Y-m-d H:i:s')."');";	
					$res6 = mysql_query($qry6);
					
					$q7 = "select pairid from STBDetails where stbid = '$osid' and cardid='$ncid';";	
					$r7 = mysql_fetch_array(mysql_query($q7));					
					$pairid = $r7['pairid'];
					
					
					$qry = "insert into STBtoCustomer (CustId,stbid,cardid,pairid,ActivationDate,TerminationDate,Ratepermonth,enteredby,entereddate,status) 
					values('$custid', '$osid','$ncid' ,'$pairid','$d->rdate',NULL, '$rpm','$by','".date('Y-m-d H:i:s')."','1');";	
					$res = mysql_query($qry);																				
					
					$qry3 = "update cardTable c set  c.flag='occ',modifiedby='$user',modifieddt='".date('Y-m-d H:i:s')."' where c.id='$ncid'";	
					$res3 = mysql_query($qry3);					
					
					$flagval = '';
					$cause = $d->reason;
					if($cause=='Faulty')
						$flagval = 'faulty';
					else if($cause=='Blacklist')						
						$flagval = 'blist';
					else
						$flagval = 'free';
					
					$qry4 = "update cardTable c set c.flag='$flagval',modifiedby='$user',modifieddt='".date('Y-m-d H:i:s')."' where  c.id='$ocid'";	
					$res4 = mysql_query($qry4);
					
					$qry5 = "update STBtoCustomer set TerminationDate='$d->rdate',modifiedby='$user',modifieddate='".date('Y-m-d H:i:s')."' where pairid = '$oldpairid'";	
					$res5 = mysql_query($qry5);
					
					$qry7 = "update STBDetails set Active='9',modifiedby='$user',modifieddate='".date('Y-m-d H:i:s')."' where pairid = '$oldpairid'";	
					$res7 = mysql_query($qry7);
					
					echo "<br> :::$qry = $res<br> ...$qry3 = $res3 <br> ...$qry4 = $res4<br> ...$qry5 = $res5 <br> ...$qry6 = $res6 <br><br>-->$qry7=$res7";
					break;
				
				case 'replaces':				
					$q3 = "select id from stbTable where stbno = '$d->newstb';";					
					$r3 = mysql_fetch_array(mysql_query($q3));	
					$nsid = $r3['id'];			
					$q3 = "select id from stbTable where stbno = '$d->oldstb';";					
					$r3 = mysql_fetch_array(mysql_query($q3));	
					$osid = $r3['id'];																			
					$r = mysql_fetch_array(mysql_query("select id from cardTable where cardno='$d->card'"));
					$ocid = $r['id'];
					
					$qry = "insert into replaces(newstb,oldstb,cardno,replacedate,reason,comments,status) 
					values ('$nsid','$osid','$ocid','$d->rdate','$d->reason','$d->comments','1');";
					$res = mysql_query($qry);
					echo $qry."---".$res."<br>";										
					
					$qry6 = "insert into STBDetails(stbid,cardid,stbtypeid,Active,Notes,enteredby,entereddate) 
					values('$nsid', '$ocid', '$stbtype', '1','created on replacement','$user','".date('Y-m-d H:i:s')."');";	
					$res6 = mysql_query($qry6);
					echo $qry6."---".$res6."<br>";
					
					$q2 = "select CustId,pairid,cardid,stbid,RateperMonth,TerminationDate,enteredby from STBtoCustomer where stbid = '$osid' and cardid='$ocid';";	
					$res2 = mysql_query($q2);					
					while($row2=mysql_fetch_array($res2)){					
						$custid = $row2['CustId'];					
						$rpm = $row2['RateperMonth'];
						$by = $row2['enteredby'];
						$tdt = $row2['TerminationDate'];
						$oldpairid=$row2['pairid'];
					}
					$q4 = "select stbtypeid from STBDetails where pairid='$oldpairid';";
					$r4 = mysql_fetch_array(mysql_query($q4));
					$stbtype = $r4['stbtypeid'];
					
					$q3 = "select pairid from STBDetails where stbid = '$nsid' and cardid='$ocid';";	
					$row3 = mysql_fetch_array(mysql_query($q3));					
					$pairid = $row3['pairid'];
					echo $q3."---id-".$pairid."<br>";
					
					$qry = "insert into STBtoCustomer (CustId,stbid,cardid,pairid,ActivationDate,TerminationDate,Ratepermonth,enteredby,entereddate,status) 
					values('$custid', '$nsid','$ocid' ,'$pairid','$d->rdate',NULL, '$rpm','$by','".date('Y-m-d H:i:s')."','1');";	
					$res = mysql_query($qry);
					echo $qry."---".$res."<br>";
					
					$qry3 = "update stbTable s set  s.flag='occ',modifiedby='$user',modifieddt='".date('Y-m-d H:i:s')."' where s.id='$nsid'";	
					$res3 = mysql_query($qry3);
					
					$flagval = '';
					$cause = $d->reason;
					if($cause=='Release')
						$flagval = 'free';
					else if($cause=='Blacklist')						
						$flagval = 'blist';
					else
						$flagval = 'faulty';
					
					$qry4 = "update stbTable set flag='$flagval',modifiedby='$user',modifieddt='".date('Y-m-d H:i:s')."' where id='$osid'";	
					$res4 = mysql_query($qry4);
					
					$qry5 = "update STBtoCustomer set TerminationDate='$d->rdate',modifiedby='$user',modifieddate='".date('Y-m-d H:i:s')."' where pairid = '$oldpairid'";	
					$res5 = mysql_query($qry5);	
					echo "<br>".$qry5."---".$res5;

					$qry6 = "update STBDetails set Active='9' where pairid = '$oldpairid'";	
					$res6 = mysql_query($qry6);
					break;
				
				case 'replacePair':						
					$r1 = mysql_fetch_array(mysql_query("select id from stbTable where stbno = '$d->newstb';"));	
					$nsid = $r1['id'];								
					$r2 = mysql_fetch_array(mysql_query("select id from stbTable where stbno = '$d->oldstb';"));	
					$osid = $r2['id'];																			
					$r3 = mysql_fetch_array(mysql_query("select id from cardTable where cardno='$d->oldcard'"));
					$ocid = $r3['id'];
					$r4 = mysql_fetch_array(mysql_query("select id from cardTable where cardno='$d->newcard'"));
					$ncid = $r4['id'];
					
					$q3 = "select pairid from STBDetails where stbid='$nsid' and cardid='$ncid';";					
					$r3 = mysql_fetch_array(mysql_query($q3));	
					$npid = $r3['pairid'];	
					
					$q4 = "select pairid from STBDetails where stbid='$osid' and cardid='$ocid';";					
					$r4 = mysql_fetch_array(mysql_query($q4));	
					$opid = $r4['pairid'];																			
					echo "<br>$q3 - $r3<br>$q4 - $r4";										
					
					$qry = "insert into replacePair (newpair,oldpair,replacedate,reason,comments,status) 
					values ('$npid','$opid','$d->rdate','$d->reason','$d->comments','1');";
					$res = mysql_query($qry);
					echo $qry."---".$res;
					
					$q2 = "select CustId,pairid,RateperMonth,TerminationDate,enteredby from STBtoCustomer where pairid = '$opid';";	
					$res2 = mysql_query($q2);					
					while($row2=mysql_fetch_array($res2)){										
						$custid = $row2['CustId'];				
						$rpm = $row2['RateperMonth'];
						$by = $row2['enteredby'];
						$tdt = $row2['TerminationDate'];
					}
					
					$qry = "insert into STBtoCustomer (CustId,pairid,stbid,cardid,ActivationDate,TerminationDate,Ratepermonth,enteredby,entereddate,status) 
					values('$custid','$npid','$nsid','$ncid','$d->rdate', case when ".strlen($tdt)." > 2 then '".date("Y-m-d H:i:s", strtotime($tdt))."' else NULL end, '$rpm','$by','".date('Y-m-d H:i:s')."','1');";	
					$res = mysql_query($qry);																
					
					$flagval = '';
					$cause = $d->reason;
					if($cause=='Release')				
						$flagval = 'free';										
					else {
						if($cause=='Blacklist')					
							$flagval = 'blist';					
						else
							$flagval = 'faulty';
						$q5 = "update STBDetails set Active='9' where pairid = '$opid'";	
						$r5 = mysql_query($q5);
						
						$qry4 = "update stbTable s,cardTable c set s.flag='$flagval',c.flag='$flagval',s.modifiedby='$user',s.modifieddt='".date('Y-m-d')."',c.modifiedby='$user',c.modifieddt='".date('Y-m-d H:i:s')."' where s.id='$osid' and c.id='$ocid'";	
						$res4 = mysql_query($qry4);
					}
					
					$qry5 = "update STBtoCustomer set TerminationDate='$d->rdate',modifiedby='$user',modifieddate='".date('Y-m-d H:i:s')."' where pairid = '$opid'";	
					$res5 = mysql_query($qry5);												
					
					/*$qry3 = "update stbTable s,cardTable c set s.flag='occ',c.flag='occ',s.modifiedby='$user',s.modifieddt='".date('Y-m-d H:i:s')."',c.modifiedby='$user',c.modifieddt='".date('Y-m-d H:i:s')."'' where s.id='$nsid' and c.id='$ncid'";	
					$res3 = mysql_query($qry3);	*/
					
					echo "<br>...$qry5 = $res5 <br>...$qry4 = $res4 <br>...$qry3 = $res3 <br>...$qry = $res";
					break;						
			}
			break;
		
		default:
			echo "no operation selected...";
	}
	
?>